Mega Code Archive
Transact SQL
1) A script that tests and adjusts credit amounts with a WHILE loop
2) A script that tests for outstanding Billings with an IF statement
3) A SQL script that uses variables
4) A stored procedure with dynamic execution
5) A stored procedure with dynamic execution by using EXEC
6) A variable can be assigned a value from a subquery
7) An alternate syntax for setting a variables value in a select list
8) Another TRYCATCH
9) Applying TRY CATCH Error Handling Without Recoding a Stored Procedure
10) Archiving end user data
11) Assign returned value from a function to a declared variable
12) Assign the function result to the variable
13) Assignment with a Subquery
14) Basic TRYCATCH
15) Begin end block
16) Block comments
17) Break out of a while loop
18) Build a dynamic sql statement from schema name, table name and column name
19) Change database dynamically
20) Common Table Expressions
21) Concatenating Variables
22) Continue a loop
23) Create a dynamic sql statement and save it in a varchar type variable
24) Creating a Comma Delimited List using SELECT
25) Creating a Table Variable to Hold a Temporary Result Set
26) Creation Script for the usp_EmployeeDelete Stored Procedure
27) Creation Script for the usp_EmployeeInsert Stored Procedure
28) Creation Script for the usp_EmployeeUpdate Stored Procedure
29) DECLARE @Out Int
30) DECLARE @Out Int (get value out of a procedure)
31) Declare a table type variable and query it
32) Delete record in a stored procedure by parameter
33) Deleting Rows in Chunks
34) Dynamic Deletes
35) Dynamic Selects
36) Error Handling in SQL Server 2005
37) Error Handling with TRY CATCH
38) Example of a Variable Not Updated Due to No Rows Being Returned
39) Execute a dynamic statement and return the result with OUTPUT
40) EXECUTE AS
41) Global variable names begin with an @@ prefix
42) Goto statement
43) perform repetitive processing
44) Use RETURN
45) IF (@au_id IS NULL)
46) IF (SELECT COUNT() FROM inserted) 1
47) IF (SELECT ID FROM inserted) like 99[0-9][0-9]
48) IF @@ERROR 0 GOTO TRAN_ABORT
49) IF EXISTS
50) If statement with aggregate function
51) Implementing the ELSE Statement In Our Sproc
52) In-line comments are placed in the body of the script to document the process
53) Inline Table-Valued Functions
54) Insert 100 rows of data
55) Insert into a table variable
56) Insert into with EXEC
57) It is possible to define several variables in a single Declare statement
58) Join tables in a procedure
59) Local variables used for searches
60) Multiple variables can be assigned values in a single operation
61) Nesting TRY CATCH Calls
62) One procedure for insert, another for update
63) Optimized procedure to select earning authors
64) Parse exception in another level of scope
65) Pass parameters into dynamic sql statement
66) Pass value into a procedure and insert it to a table
67) Pass variable to a user-defined function
68) PRINT command
69) PRINT string_expression
70) PRINT Today is + CONVERT(char(30), GETDATE())
71) Print debug string out of a code block
72) Printing with variables
73) Procedure for bulk insert
74) RAISERROR (50001, 15, -1, @parm1, @parm2)
75) RAISERROR syntax
76) Raise error in case of error
77) Raise error out of a procedure
78) Raise exception with parameters
79) Raising a message not defined in sysmessages
80) Remove data based upon criteria specified through arguments
81) Return a table from a function
82) RETURN also allows for an optional integer expression
83) RETURN is used to exit the current Transact-SQL batch, query, or stored procedure immediately
84) Return statement
85) Return the variable value
86) RETURNS a TABLE with structure
87) Select from Common Table Expressions
88) SELECT LEFT(@FullName, 5)
89) SELECT query with a variable for the table name so that condition determines which table the result set is based on
90) Sending Variable Information to an EXEC() Call
91) SET variable and column in one command
92) Short circuit aborts any further processing of a logical expression as soon as its result can be determined
93) Simple SELECT query that uses a variable for the field to sequence the result set
94) Simple SELECT query written using variables for field names
95) Statement Limit
96) Stored Procedure to Retrieve a Customers Most Recent Order
97) Switch database in EXEC
98) Syntax for a CTE for recursive queries is
99) Syntax for Variable Assignment with the SELECT Statement
100) Syntax for Variable Assignment with the SET Statement
101) Testing the ISNULL function
102) The DECLARE statement has a pretty simple syntax
103) The names of variables must begin with @
104) The syntax of the DECLARE statement for a table variable
105) The syntax of the EXEC statement
106) The syntax of the IF ELSE statement
107) The syntax of the RAISERROR statement
108) The TIME Parameter
109) The WAITFOR Statement
110) This example is a simple stored procedure using a generic approach to error handling
111) This procedure inserts rows by using while loop
112) Try catch Demo
113) Use a stored procedure to insert data to a table
114) Use aggregate function with variables
115) Use function returned value
116) Use if and like to check a pattern
117) Use if statement to check a variable
118) Use Local Variables in where clause
119) Use more than one sql statement in EXEC
120) Use one variables in like function
121) Use right function with a variable
122) Use select to output a variable
123) Use set command to update a variable
124) Use Table Variables as a table
125) Use while loop to insert data
126) Uses an IF ELSE statement
127) Using a Subquery with a Single-Statement WHILE Loop
128) Using an Expression with an Explicit Unknown Value
129) Using an Expression with an Unknown Value Returned from One of the Participating Simple Logical Expressions
130) Using EXEC () to Execute a Dynamic TOP n Query
131) Using GOTO
132) Using RAISERROR with the SETERROR Option
133) Using RAISERROR without an Error Number
134) Using SELECT to Assign Variables
135) Using SET to Assign Variables
136) Using Stored Procedure to create dynamic sql statement
137) Using the INSERT DEFAULT VALUES Statement
138) Using WAITFOR
139) Varchar type variable
140) WAITFOR DELAY 00
141) When multiple lines of code follow an IF statement it is best to wrap the lines in a BEGIN END block
142) WHILE @@FETCH_STATUS = 0
143) WHILE with AND operator
144) While loop controlled by an aggregate function
145) While with counter
146) With as
147) Working with Variables
148) XML type variable